package easybooking.db;

import easybooking.data.Airline;
import easybooking.data.Airport;
import easybooking.data.Customer;
import easybooking.data.Plane;
import easybooking.data.Flight;
import easybooking.data.FlightImpl;
import java.rmi.RemoteException;
import java.text.ParseException;
import java.util.Vector;
import java.util.Date;
import java.sql.*;

/**
 * Mapper-Class, which displays <code>Flight</code> objects in a relational
 * database. Therefore a collection of methods to search, create and modify
 * will be provided.
 * The mapping is bidirectional, which means, that objects could be transformed
 * into database-structures and database-structures into objects.
 *
 * @author Mark Roman Stehle
 */
// <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
// #[regen=yes,id=DCE.1086D68F-A06F-6D58-CF5E-B03BAD670B3B]
// </editor-fold> 
public class FlightMapper extends Mapper {

    /**
     * The class <code>FlightMapper</code> will be instantiated only once.
     * This is called <b>Singleton</b>, because of the <code>static</code>-tag,
     * the variable exists only once for all instances of this class. The
     * variable saves the only instance of this class.
     *
     * @see flightMapper()
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.6B27F562-2C18-9A54-E907-45EAA3F07E0C]
    // </editor-fold> 
    private static FlightMapper flightMapper = null;

    /**
     * The protected constructor prevents the possibility to generate a new
     * instance of this class.
     */
    protected FlightMapper() {
    }

    /**
     * The static method can be called with
     * <code>FlightMapper.flightMapper()</code>.
     * This guarantees the singleton property by checking so that only one
     * instance of the <code>FlightMapper</code> exists.

     * <b>Conclusion:</b> The <code>FlightMapper</code> should not be
     * instantiated with <code>new</code>, but rather by calling the static
     * method.

     * @return The <code>FlightMapper</code> object.
     * @see flightMapper
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.CB1DC438-3F1F-53AA-52E9-5605698FFD11]
    // </editor-fold> 
    public static FlightMapper flightMapper() {
        if (flightMapper == null) {
            flightMapper = new FlightMapper();
        }
        return flightMapper;
    }

    /**
     * Searching a flight with given key, since the key is distinct only one
     * object will be returned.
     *
     * @param id primary key (->DB)
     * @return Flight object, which corresponds the given key,
     * <code>null</code> for a non existing database tupel.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.DDA7F512-BF8A-EB91-8D92-9DE921D9F261]
    // </editor-fold> 
    public Flight findByKey(int id) {
        Connection con = DBConnection.connection();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT flightId, dateTime, planeId, startAirportId, destAirportId, isActive FROM Flight WHERE flightId=" + id);
            if (rs.next()) {
                try {
                    Flight f = new FlightImpl();
                    f.setFlightId(rs.getInt("flightId"));
                    f.setDateTime(sqlDateToUtilDate(rs.getTimestamp("dateTime")));
                    f.setPlaneId((rs.getInt("planeId")));
                    f.setStartAirportId(rs.getInt("startAirportId"));
                    f.setDestAirportId(rs.getInt("destAirportId"));
                    f.setIsActive(rs.getBoolean("isActive"));
                    return f;
                } catch (Exception ex) {
                    ex.printStackTrace();
                    return null;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
            return null;
        }
        return null;
    }

    /**
     * Selecting all active flights from the database.
     *
     * @return A vector of flight objects, which includes all active flights.
     * In case of an exception, an empty vector will be returned.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.11E887C6-75F5-8AB8-F9F1-30511ADFEB8B]
    // </editor-fold> 
    public Vector<Flight> findAll() {
        Connection con = DBConnection.connection();
        Vector<Flight> result = new Vector<Flight>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT flightId, dateTime, planeId, startAirportId, destAirportId, isActive FROM Flight " +
                    "WHERE isActive = true " +
                    "ORDER BY flightId ASC");
            while (rs.next()) {
                try {
                    Flight f = new FlightImpl();
                    f.setFlightId(rs.getInt("flightId"));
                    f.setDateTime(sqlDateToUtilDate(rs.getTimestamp("dateTime")));
                    f.setPlaneId((rs.getInt("planeId")));
                    f.setStartAirportId(rs.getInt("startAirportId"));
                    f.setDestAirportId(rs.getInt("destAirportId"));
                    f.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(f);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return result;
    }

    /**
     * Inserting a <code>Flight</code> object into the database. Before that,
     * the primary key of the given object will be verified und corrected.
     *
     * @param f the object to be saved
     * @return The given object, with corrected primary key.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.7E50195A-C4A3-2E35-0242-0247DD4972A1]
    // </editor-fold> 
    public Flight insert(Flight f) {
        Connection con = DBConnection.connection();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT MAX(flightId) AS maxFlightId " +
                    "FROM Flight");
            if (rs.next()) {
                try {
                    f.setFlightId(rs.getInt("maxFlightId") + 1);
                    stmt = con.createStatement();
                    stmt.executeUpdate("INSERT INTO Flight (flightId, dateTime, planeId, startAirportId, destAirportId , isActive) " +
                            "VALUES (" +
                            f.getFlightId() + ",'" +
                            utilDateToSqlDate(f.getDateTime()) + "','" +
                            f.getPlaneId() + "','" +
                            f.getStartAirportId() + "','" +
                            f.getDestAirportId() + "','" +
                            booleanToInt(f.getIsActive()) + "')");
                } catch (RemoteException ex) {
                    ex.printStackTrace();
                } catch (ParseException pe) {
                    pe.printStackTrace();
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return f;
    }

    /**
     * Rewriting an object into the database.
     *
     * @param f the object which sould be written into the database
     * @return The given object.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.76D6DF2A-AB74-331C-3550-205A9346D44E]
    // </editor-fold> 
    public Flight update(Flight f) {
        Connection con = DBConnection.connection();
        try {
            Statement stmt = con.createStatement();
            stmt.executeUpdate("UPDATE Flight " +
                    "SET dateTime=\"" + utilDateToSqlDate(f.getDateTime()) + "\", " +
                    "planeId=\"" + f.getPlaneId() + "\", " +
                    "startAirportId=\"" + f.getStartAirportId() + "\", " +
                    "destAirportId=\"" + f.getDestAirportId() + "\", " +
                    "isActive=\"" + booleanToInt(f.getIsActive()) + "\" " +
                    "WHERE flightId=" + f.getFlightId());
        } catch (SQLException e2) {
            e2.printStackTrace();
        } catch (RemoteException e) {
            e.printStackTrace();
        } catch (ParseException pe) {
            pe.printStackTrace();
        }
        return f;
    }

    /**
     * Selecting all active flights from the database with a certain date,
     * which is given through a parameter.
     *
     * @param date the date which should be searched for
     * @return A vector of flight objects, which includes all active flights
     * with a certain date.
     * In case of an exception, an empty vector will be returned.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.FF6C0887-DFFB-0CD1-CEA2-DE12A5F283A2]
    // </editor-fold> 
    public Vector<Flight> findByDate(Date date) {
        Connection con = DBConnection.connection();
        Vector<Flight> result = new Vector<Flight>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT flightId, dateTime, planeId, startAirportId, destAirportId, isActive FROM Flight " +
                    "WHERE dateTime=" + utilDateToSqlDate(date) +
                    " AND isActive = true " +
                    "ORDER BY flightId ASC");
            while (rs.next()) {
                try {
                    Flight f = new FlightImpl();
                    f.setFlightId(rs.getInt("flightId"));
                    f.setDateTime(sqlDateToUtilDate(rs.getTimestamp("dateTime")));
                    f.setPlaneId((rs.getInt("planeId")));
                    f.setStartAirportId(rs.getInt("startAirportId"));
                    f.setDestAirportId(rs.getInt("destAirportId"));
                    f.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(f);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        } catch (ParseException pe) {
            pe.printStackTrace();
        }
        return result;
    }

    /**
     * Selecting all active flights from the database by a certain plane,
     * which is given through a parameter.
     *
     * @param planeId the id of the plane by which the flights
     * should be searched
     * @return A vector of flight objects, which includes all active flights
     * by a certain plane.
     * In case of an exception, an empty vector will be returned.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.751103D7-4688-B30B-942C-F2C5C199C011]
    // </editor-fold> 
    public Vector<Flight> findByPlane(int planeId) {
        Connection con = DBConnection.connection();
        Vector<Flight> result = new Vector<Flight>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT flightId, dateTime, planeId, startAirportId, destAirportId, isActive FROM Flight " +
                    "WHERE planeId=" + planeId +
                    " AND isActive = true " +
                    "ORDER BY flightId ASC");
            while (rs.next()) {
                try {
                    Flight f = new FlightImpl();
                    f.setFlightId(rs.getInt("flightId"));
                    f.setDateTime(sqlDateToUtilDate(rs.getTimestamp("dateTime")));
                    f.setPlaneId((rs.getInt("planeId")));
                    f.setStartAirportId(rs.getInt("startAirportId"));
                    f.setDestAirportId(rs.getInt("destAirportId"));
                    f.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(f);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return result;
    }

    /**
     * Selecting all active flights from the database by a certain plane,
     * which is given through a parameter.
     *
     * @param p the object of a plane by which the flights
     * should be searched
     * @return A vector of flight objects, which includes all active flights
     * by a certain plane. In case of an exception, an empty vector will be
     * returned.
     * @see findByPlane(int planeId)
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker ">
    // #[regen=yes,id=DCE.13EC0E44-4F12-39D0-5345-B1F3C37F5444]
    // </editor-fold>
    public Vector<Flight> findByPlane(Plane p) {
        try {
            return FlightMapper.flightMapper().findByPlane(p.getPlaneId());
        } catch (RemoteException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * Selecting all active flights from the database by a certain start
     * airport, which is given through a parameter.
     *
     * @param startAirportId the id of the airport from which the flights
     * starts
     * @return A vector of flight objects, which includes all active flights
     * from a certain start airport.
     * In case of an exception, an empty vector will be returned.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.D3B32DFE-6614-53EA-F78F-CE53EDC02A3D]
    // </editor-fold> 
    public Vector<Flight> findByStartAirport(int airportId) {
        Connection con = DBConnection.connection();
        Vector<Flight> result = new Vector<Flight>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT flightId, dateTime, planeId, startAirportId, destAirportId, isActive FROM Flight " +
                    "WHERE startAirportId=" + airportId +
                    " AND isActive = true " +
                    "ORDER BY flightId ASC");
            while (rs.next()) {
                try {
                    Flight f = new FlightImpl();
                    f.setFlightId(rs.getInt("flightId"));
                    f.setDateTime(sqlDateToUtilDate(rs.getTimestamp("dateTime")));
                    f.setPlaneId((rs.getInt("planeId")));
                    f.setStartAirportId(rs.getInt("startAirportId"));
                    f.setDestAirportId(rs.getInt("destAirportId"));
                    f.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(f);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return result;
    }

    /**
     * Selecting all active flights from the database by a certain start
     * airport, which is given through a parameter.
     *
     * @param a the object of the airport from which the flights
     * starts
     * @return A vector of flight objects, which includes all active flights
     * from a certain start airport. In case of an exception, an empty vector
     * will be returned.
     * @see findByStartAirport(int airportId)
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker ">
    // #[regen=yes,id=DCE.AA47BFA8-6A36-50D3-81E8-31F60846F77E]
    // </editor-fold>
    public Vector<Flight> findByStartAirport(Airport a) {
        try {
            return FlightMapper.flightMapper().findByStartAirport(a.getAirportId());
        } catch (RemoteException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * Selecting all active flights from the database by a certain destination
     * airport, which is given through a parameter.
     *
     * @param destAirportId the id of the airport to which the flights
     * goes.
     * @return A vector of flight objects, which includes all active flights
     * to a certain destination airport.
     * In case of an exception, an empty vector will be returned.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.1FCDD2B0-9E1D-E2AB-F3D6-0D4DAA75997C]
    // </editor-fold> 
    public Vector<Flight> findByDestAirport(int airportId) {
        Connection con = DBConnection.connection();
        Vector<Flight> result = new Vector<Flight>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT flightId, dateTime, planeId, startAirportId, destAirportId, isActive FROM Flight " +
                    "WHERE destAirportId=" + airportId +
                    " AND isActive = true " +
                    "ORDER BY flightId ASC");
            while (rs.next()) {
                try {
                    Flight f = new FlightImpl();
                    f.setFlightId(rs.getInt("flightId"));
                    f.setDateTime(sqlDateToUtilDate(rs.getTimestamp("dateTime")));
                    f.setPlaneId((rs.getInt("planeId")));
                    f.setStartAirportId(rs.getInt("startAirportId"));
                    f.setDestAirportId(rs.getInt("destAirportId"));
                    f.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(f);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return result;
    }

    /**
     * Selecting all active flights from the database by a certain destination
     * airport, which is given through a parameter.
     *
     * @param a the object of the airport to which the flights
     * goes
     * @return A vector of flight objects, which includes all active flights
     * to a certain destination airport. In case of an exception, an empty
     * vector will be returned.
     * @see findByDestAirport(int airportId)
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker ">
    // #[regen=yes,id=DCE.3E5F39E6-8F87-5E64-8864-AF72AC14B347]
    // </editor-fold> 
    public Vector<Flight> findByDestAirport(Airport a) {
        try {
            return FlightMapper.flightMapper().findByDestAirport(a.getAirportId());
        } catch (RemoteException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * Selecting all active flights from the database by a certain customer,
     * which is given through a parameter.
     *
     * @param customerId the id of a customer by which the flights
     * should be searched
     * @return A vector of flight objects, which includes all active flights
     * by a certain customer.
     * In case of an exception, an empty vector will be returned.
     */
    public Vector<Flight> findByCustomer(int customerId) {
        Connection con = DBConnection.connection();
        Vector<Flight> result = new Vector<Flight>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT f.flightId, f.dateTime, f.planeId, f.startAirportId, f.destAirportId, f.isActive FROM Flight f " +
                    "LEFT JOIN Booking b ON b.flightId = f.flightId " +
                    "WHERE b.customerId=" + customerId +
                    " AND b.isActive = true " +
                    "ORDER BY f.flightId ASC");
            while (rs.next()) {
                try {
                    Flight f = new FlightImpl();
                    f.setFlightId(rs.getInt("flightId"));
                    f.setDateTime(sqlDateToUtilDate(rs.getTimestamp("dateTime")));
                    f.setPlaneId((rs.getInt("planeId")));
                    f.setStartAirportId(rs.getInt("startAirportId"));
                    f.setDestAirportId(rs.getInt("destAirportId"));
                    f.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(f);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return result;
    }

    /**
     * Selecting all active flights from the database by a certain customer,
     * which is given through a parameter.
     *
     * @param c the object of a customer by which the flights
     * should be searched
     * @return A vector of flight objects, which includes all active flights
     * by a certain customer. In case of an exception, an empty vector will
     * be returned.
     * @see findByCustomer(int customerId)
     */
    public Vector<Flight> findByCustomer(Customer c) {
        try {
            return FlightMapper.flightMapper().findByCustomer(c.getCustomerId());
        } catch (RemoteException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * Selecting all active flights from the database by a certain customer
     * and a certain airline which are given through parameters.
     *
     * @param customerId the id of a customer by which the flights
     * should be searched
     * @param airlineId the id of an airline by which the flights
     * should be searched
     * @return A vector of flight objects, which includes all active flights
     * by a certain customer and a certain airline.
     * In case of an exception, an empty vector will be returned.
     */
    public Vector<Flight> findByCustomerAndAirline(int customerId, int airlineId) {
        Connection con = DBConnection.connection();
        Vector<Flight> result = new Vector<Flight>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT f.flightId, f.dateTime, f.planeId, f.startAirportId, f.destAirportId, f.isActive FROM Flight f " +
                    "LEFT JOIN Booking b ON b.flightId = f.flightId " +
                    "LEFT JOIN Plane p ON p.planeId = f.planeId " +
                    "WHERE b.customerId=" + customerId +
                    " AND p.airlineId=" + airlineId +
                    " AND f.isActive = true " +
                    " ORDER BY f.flightId ASC");
            while (rs.next()) {
                try {
                    Flight f = new FlightImpl();
                    f.setFlightId(rs.getInt("flightId"));
                    f.setDateTime(sqlDateToUtilDate(rs.getTimestamp("dateTime")));
                    f.setPlaneId((rs.getInt("planeId")));
                    f.setStartAirportId(rs.getInt("startAirportId"));
                    f.setDestAirportId(rs.getInt("destAirportId"));
                    f.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(f);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return result;
    }

    /**
     * Selecting all active flights from the database by a certain customer
     * and a certain airline which are given through parameters.
     *
     * @param c the object of a customer by which the flights
     * should be searched
     * @param a the object of an airline by which the flights
     * should be searched
     * @return A vector of flight objects, which includes all active flights
     * by a certain customer and a certain airline. In case of an exception,
     * an empty vector will be returned.
     * @see findByCustomerAndAirline(int customerId, int airlineId)
     */
    public Vector<Flight> findByCustomerAndAirline(Customer c, Airline a) {
        try {
            return FlightMapper.flightMapper().findByCustomerAndAirline(c.getCustomerId(), a.getAirlineId());
        } catch (RemoteException e) {
            e.printStackTrace();
            return null;
        }
    }
    
    /**
     * Selecting all active flights from the database by a certain customer
     * and a certain airline and between two dates which are given through
     * parameters.
     *
     * @param customerId the id of a customer by which the flights
     * should be searched
     * @param airlineId the id of an airline by which the flights
     * should be searched
     * @param fromDate the first date of the dates between the flights should
     * be searched
     * @param toDate the second date of the dates between the flights should
     * be searched
     * @return A vector of flight objects, which includes all active flights
     * by a certain customer and a certain airline between two dates.
     * In case of an exception, an empty vector will be returned.
     */
    public Vector<Flight> findByCustomerAndAirlineAndPeriod(int customerId, int airlineId, Date fromDate, Date toDate) {
        Connection con = DBConnection.connection();
        Vector<Flight> result = new Vector<Flight>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT f.flightId, f.dateTime, f.planeId, f.startAirportId, f.destAirportId, f.isActive FROM Flight f " +
                    "LEFT JOIN Booking b ON b.flightId = f.flightId " +
                    "LEFT JOIN Plane p ON p.planeId = f.planeId " +
                    "WHERE b.customerId=" + customerId +
                    " AND p.airlineId=" + airlineId +
                    " AND f.isActive = true " +
                    " AND f.dateTime between '" + utilDateToSqlDate(fromDate) + "'" +
                    " AND '" + utilDateToSqlDate(toDate) + "'" +
                    " ORDER BY f.flightId ASC");
            while (rs.next()) {
                try {
                    Flight f = new FlightImpl();
                    f.setFlightId(rs.getInt("flightId"));
                    f.setDateTime(sqlDateToUtilDate(rs.getTimestamp("dateTime")));
                    f.setPlaneId((rs.getInt("planeId")));
                    f.setStartAirportId(rs.getInt("startAirportId"));
                    f.setDestAirportId(rs.getInt("destAirportId"));
                    f.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(f);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
         catch (ParseException pe) {
            pe.printStackTrace();
        }
        return result;
    }
    
    /**
     * Selecting all active flights from the database by a certain customer
     * and a certain airline between two dates which are given through
     * parameters.
     *
     * @param c the object of a customer by which the flights
     * should be searched
     * @param a the object of an airline by which the flights
     * should be searched
     * @param fromDate the first date of the dates between the flights should
     * be searched
     * @param toDate the second date of the dates between the flights should
     * be searched
     * @return A vector of flight objects, which includes all active flights
     * by a certain customer and a certain airline between two dates. In case
     * of an exception, an empty vector will be returned.
     * @see findByCustomerAndAirline(int customerId, int airlineId,
     * Date fromDate, Date toDate)
     */
    public Vector<Flight> findByCustomerAndAirlineAndPeriod(Customer c, Airline a, Date fromDate, Date toDate) {
        try {
            return FlightMapper.flightMapper().findByCustomerAndAirlineAndPeriod(c.getCustomerId(), a.getAirlineId(), fromDate, toDate);
        } catch (RemoteException e) {
            e.printStackTrace();
            return null;
        }
    }
    
    /**
     * Selecting all active flights from the database by a certain customer
     * between two dates, which are given through parameters.
     *
     * @param customerId the id of a customer by which the flights
     * should be searched
     * @param fromDate the first date of the dates between the flights should
     * be searched
     * @param toDate the second date of the dates between the flights should
     * be searched
     * @return A vector of flight objects, which includes all active flights
     * by a certain customer and between two dates.
     * In case of an exception, an empty vector will be returned.
     */
    public Vector<Flight> findByCustomerAndPeriod(int customerId, Date fromDate, Date toDate) {
        Connection con = DBConnection.connection();
        Vector<Flight> result = new Vector<Flight>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT f.flightId, f.dateTime, f.planeId, f.startAirportId, f.destAirportId, f.isActive FROM Flight f " +
                    "LEFT JOIN Booking b ON b.flightId = f.flightId " +
                    "WHERE b.customerId=" + customerId +
                    " AND b.isActive = true " +
                    " AND f.dateTime between '" + utilDateToSqlDate(fromDate) + "'" +
                    " AND '" + utilDateToSqlDate(toDate) + "'" +
                    " ORDER BY f.flightId ASC");
            while (rs.next()) {
                try {
                    Flight f = new FlightImpl();
                    f.setFlightId(rs.getInt("flightId"));
                    f.setDateTime(sqlDateToUtilDate(rs.getTimestamp("dateTime")));
                    f.setPlaneId((rs.getInt("planeId")));
                    f.setStartAirportId(rs.getInt("startAirportId"));
                    f.setDestAirportId(rs.getInt("destAirportId"));
                    f.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(f);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
         catch (ParseException pe) {
            pe.printStackTrace();
        }
        return result;
    }
    
    /**
     * Selecting all active flights from the database by a certain customer,
     * between two dates, which are given through parameters.
     *
     * @param c the object of a customer by which the flights
     * should be searched
     * @param fromDate the first date of the dates between the flights should
     * be searched
     * @param toDate the second date of the dates between the flights should
     * be searched
     * @return A vector of flight objects, which includes all active flights
     * by a certain customer between two dates. In case of an exception, an
     * empty vector will be returned.
     * @see findByCustomer(int customerId)
     */
    public Vector<Flight> findByCustomerAndPeriod(Customer c, Date fromDate, Date toDate) {
        try {
            return FlightMapper.flightMapper().findByCustomerAndPeriod(c.getCustomerId(), fromDate, toDate);
        } catch (RemoteException e) {
            e.printStackTrace();
            return null;
        }
    }
}